﻿CREATE PROCEDURE [dbo].[proc_Customer_Getlist_Comsume_In]
(
		@InCompanyId varchar(500),
		@Stype nvarchar(50),
		@Stext nvarchar(50),

		@StartDate nvarchar(10),
		@EndDate nvarchar(10),
		@StartIndex int,
		@EndIndex int
	)
AS
Begin
DECLARE @cSql nvarchar(4000),@cWhere nvarchar(1000)
If @StartDate='2006-01-01'
	Set @cWhere = 'CompanyId In('+@InCompanyId+')'
Else
	Set @cWhere = 'CompanyId In('+@InCompanyId+') And CreateDate>=@sRq And CreateDate<=@eRq'


If @Stext<>''
	SET @cWhere=@cWhere+' And '+@Stype+' Like ''%'+@Stext+'%'' '

	--Set @cSql='Declare @sRq Datetime,@eRq Datetime
	--Set @sRq = Convert(Datetime,''' + @StartDate + ' 00:00:00'')
	--Set @eRq = Convert(Datetime,''' + @EndDate + ' 23:59:59'')

		
	--;WITH List As(Select ROW_NUMBER() OVER (Order By Id Desc) AS Row,
	--	Id,CustomerId,CustomerName,
	--	(select sum(F_YingShou) from [Order]  ord where ord.CusId=c.CustomerId and StateId>1) as ComsumePrice

	--	--((Select sum( saleprice) From Project pr Where pr.CusId=C.CustomerId  and (IsHedge=0 Or IsHedge=-2))
	--	--+
	--	--(Select sum( saleprice) From Project pr Where pr.CusId=C.CustomerId  and IsHedge=-1)) 
	--	--as ComsumePrice
		
	--From Customer C
	--Where '+@cWhere+'
	--)
	--Select Id,CustomerId,CustomerName,isnull(ComsumePrice,0) as ComsumePrice,	
	--'Set @cSql=@cSql+'
	
	--(Select Count(0) From List) As RecordCount
	--From List 
	--Where Row Between '+Convert(varchar(10),@StartIndex)+' And '+Convert(varchar(10),@EndIndex) + '
	--Order By ComsumePrice desc'
   
	--Exec(@cSql)
	Set @cSql='Declare @sRq Datetime,@eRq Datetime
	Set @sRq = Convert(Datetime,''' + @StartDate + ' 00:00:00'')
	Set @eRq = Convert(Datetime,''' + @EndDate + ' 23:59:59'')
	;WITH List As(Select ROW_NUMBER() OVER (Order By sum(f_yingshou) Desc) AS Row,
	
    CusName,
    sum(f_yingshou) as ComsumePrice

    from [Order] where '+@cWhere+' and StateId>1 group by CusName
	)
	
	'Set @cSql=@cSql+'
	Select CusName,ComsumePrice,(Select Count(0) From List) As RecordCount
    from List
	where Row Between '+Convert(varchar(10),@StartIndex)+' And '+Convert(varchar(10),@EndIndex) + '
	 order by ComsumePrice desc
	'
	

Exec(@cSql)
--Print(@cSql)
--Return(@cSql)
End
